import 'package:flutter/material.dart';
import 'package:management/model/gate_model.dart';
import 'package:management/model/visitor_model.dart';
import 'package:sqflite/sqflite.dart';

///开闸记录表
final String tableGateRecord = "gateRecord";

///开闸时间
final String columnGateRecordTime = "time";

///mac 地址
final String columnGateRecordGateMac = "mac";

///通行人员
final String columnGateRecordPasserby = "passerby";

///操作人员
final String columnGateRecordOperator = "operator";

///控制方式
final String columnGateRecordControlType = "controlType";

///开闸记录模型
class GateRecordModel {
  GateRecordModel({
    @required this.time,
    @required this.gateMac,
    this.passerby,
    @required this.operator,
    this.controlType,
    this.gateName,
    this.selected = false,
  });

  ///手动 0、语音 1
  int? controlType;

  /// 闸门名称
  String? gateName;

  /// 开闸时间
  String? time;

  ///闸门mac地址
  String? gateMac;

  ///通行人员
  String? passerby;

  ///操作人员
  String? operator;

  bool? selected;

  Map<String, Object?> toMap() {
    var map = <String, Object?>{
      columnGateRecordTime: time,
      columnGateRecordGateMac: gateMac,
      columnGateRecordPasserby: passerby,
      columnGateRecordOperator: operator,
      columnGateRecordControlType: controlType,
      columnGateName: gateName,
    };
    return map;
  }

  GateRecordModel.fromMap(Map map) {
    time = map[columnGateRecordTime];
    gateMac = map[columnGateRecordGateMac];
    passerby = map[columnGateRecordPasserby];
    operator = map[columnGateRecordOperator];
    controlType = map[columnGateRecordControlType];
    gateName = map[columnGateName];
    selected = false;
  }
}

class GateRecordProvider {
  GateRecordProvider._();

  Database? db;
  static final GateRecordProvider instance = GateRecordProvider._();

  Future<int> insert(GateRecordModel gateRecordModel) async {
    await open();
    int count = await db!.insert(tableGateRecord, gateRecordModel.toMap());
    close();
    return count;
  }

  Future<List<GateRecordModel>?> queryAll() async {
    await open();
    List<Map> maps = await db!.rawQuery("select * from $tableGateRecord");
    if (maps.length > 0) {
      List<GateRecordModel> allGateRecords = [];
      maps.forEach((element) {
        allGateRecords.add(GateRecordModel.fromMap(element));
      });
      close();
      return allGateRecords;
    }
    close();
    return null;
  }

  Future<List<GateRecordModel>?> queryByOperator(String operator) async {
    await open();
    List maps = await db!.query(tableGateRecord,
        columns: [
          columnGateRecordTime,
          columnGateRecordGateMac,
          columnGateRecordOperator,
          columnGateRecordPasserby,
          columnGateName,
        ],
        where: "$columnGateRecordOperator = ?",
        whereArgs: [operator]);
    if (maps.length > 0) {
      List<GateRecordModel> gateRecords = [];
      maps.forEach((element) {
        gateRecords.add(GateRecordModel.fromMap(element));
      });
      close();
      return gateRecords;
    }
    close();
    return null;
  }

  Future delete(GateRecordModel gateRecordModel) async {
    await open();
    int count = await db!.delete(tableGateRecord,
        where:
            '$columnGateRecordTime = ? and $columnGateRecordGateMac = ? and $columnGateRecordControlType = ?'
            ' and $columnGateRecordPasserby = ? and $columnGateRecordOperator = ? and $columnGateName = ?',
        whereArgs: [
          gateRecordModel.time,
          gateRecordModel.gateMac,
          gateRecordModel.controlType,
          gateRecordModel.passerby,
          gateRecordModel.operator,
          gateRecordModel.gateName
        ]);

    return count;
  }

  Future rawDelete() async {
    await open();
    await db!.rawDelete("delete $tableGateRecord where ");
  }

  Future open() async {
    db = await openDatabase(dbPath!);
  }

  ///关闭数据库
  Future close() async => db!.close();

  Future<List<GateRecordModel>?> queryByCondition(
      {String? beginTime,
      String? endTime,
      String? operator,
      String? passerby}) async {
    await open();
    List maps;
    if (beginTime!.isNotEmpty) {
      if (endTime!.isNotEmpty) {
        if (operator!.isNotEmpty) {
          if (passerby!.isNotEmpty) {
            maps = await db!.query(tableGateRecord,
                columns: [
                  columnGateRecordTime,
                  columnGateRecordGateMac,
                  columnGateRecordOperator,
                  columnGateRecordPasserby,
                  columnGateName,
                  columnGateRecordControlType,
                ],
                where:
                    "$columnGateRecordOperator = ? and $columnGateRecordPasserby = ? and $columnGateRecordTime between ? and ?",
                whereArgs: [operator, passerby, beginTime, endTime]);
          } else {
            maps = await db!.query(tableGateRecord,
                columns: [
                  columnGateRecordTime,
                  columnGateRecordGateMac,
                  columnGateRecordOperator,
                  columnGateRecordPasserby,
                  columnGateName,
                  columnGateRecordControlType,
                ],
                where:
                    "$columnGateRecordOperator = ?  and $columnGateRecordTime between ? and ?",
                whereArgs: [operator, beginTime, endTime]);
          }
        } else {
          if (passerby!.isNotEmpty) {
            maps = await db!.query(tableGateRecord,
                columns: [
                  columnGateRecordTime,
                  columnGateRecordGateMac,
                  columnGateRecordOperator,
                  columnGateRecordPasserby,
                  columnGateName,
                  columnGateRecordControlType,
                ],
                where:
                    "$columnGateRecordPasserby = ? and $columnGateRecordTime between ? and ?",
                whereArgs: [passerby, beginTime, endTime]);
          } else {
            maps = await db!.query(tableGateRecord,
                columns: [
                  columnGateRecordTime,
                  columnGateRecordGateMac,
                  columnGateRecordOperator,
                  columnGateRecordPasserby,
                  columnGateName,
                  columnGateRecordControlType,
                ],
                where: "$columnGateRecordTime between ? and ?",
                whereArgs: [beginTime, endTime]);
          }
        }
      } else {
        if (operator!.isNotEmpty) {
          if (passerby!.isNotEmpty) {
            maps = await db!.query(tableGateRecord,
                columns: [
                  columnGateRecordTime,
                  columnGateRecordGateMac,
                  columnGateRecordOperator,
                  columnGateRecordPasserby,
                  columnGateName,
                  columnGateRecordControlType,
                ],
                where:
                    "$columnGateRecordOperator = ? and $columnGateRecordPasserby = ? $columnGateRecordPasserby = ? and $columnGateRecordTime > ?",
                whereArgs: [operator, passerby, beginTime]);
          } else {
            maps = await db!.query(tableGateRecord,
                columns: [
                  columnGateRecordTime,
                  columnGateRecordGateMac,
                  columnGateRecordOperator,
                  columnGateRecordPasserby,
                  columnGateName,
                  columnGateRecordControlType,
                ],
                where:
                    "$columnGateRecordOperator = ? and  $columnGateRecordPasserby = ? and $columnGateRecordTime > ?",
                whereArgs: [operator, beginTime]);
          }
        } else {
          if (passerby!.isNotEmpty) {
            maps = await db!.query(tableGateRecord,
                columns: [
                  columnGateRecordTime,
                  columnGateRecordGateMac,
                  columnGateRecordOperator,
                  columnGateRecordPasserby,
                  columnGateName,
                  columnGateRecordControlType,
                ],
                where:
                    "$columnGateRecordPasserby = ? $columnGateRecordPasserby = ? and $columnGateRecordTime < ?",
                whereArgs: [passerby, beginTime]);
          } else {
            maps = await db!.query(tableGateRecord,
                columns: [
                  columnGateRecordTime,
                  columnGateRecordGateMac,
                  columnGateRecordOperator,
                  columnGateRecordPasserby,
                  columnGateName,
                  columnGateRecordControlType,
                ],
                where: "$columnGateRecordTime > ?",
                whereArgs: [beginTime]);
          }
        }
      }
    } else {
      if (endTime!.isNotEmpty) {
        if (operator!.isNotEmpty) {
          if (passerby!.isNotEmpty) {
            maps = await db!.query(tableGateRecord,
                columns: [
                  columnGateRecordTime,
                  columnGateRecordGateMac,
                  columnGateRecordOperator,
                  columnGateRecordPasserby,
                  columnGateName,
                  columnGateRecordControlType,
                ],
                where:
                    "$columnGateRecordOperator = ? and $columnGateRecordPasserby = ? $columnGateRecordPasserby = ? and $columnGateRecordTime < ?",
                whereArgs: [operator, passerby, endTime]);
          } else {
            maps = await db!.query(tableGateRecord,
                columns: [
                  columnGateRecordTime,
                  columnGateRecordGateMac,
                  columnGateRecordOperator,
                  columnGateRecordPasserby,
                  columnGateName,
                  columnGateRecordControlType,
                ],
                where:
                    "$columnGateRecordOperator = ?  and $columnGateRecordTime < ?",
                whereArgs: [operator, endTime]);
          }
        } else {
          if (passerby!.isNotEmpty) {
            maps = await db!.query(tableGateRecord,
                columns: [
                  columnGateRecordTime,
                  columnGateRecordGateMac,
                  columnGateRecordOperator,
                  columnGateRecordPasserby,
                  columnGateName,
                  columnGateRecordControlType,
                ],
                where:
                    "$columnGateRecordPasserby = ? $columnGateRecordPasserby = ? and $columnGateRecordTime < ?",
                whereArgs: [passerby, endTime]);
          } else {
            maps = await db!.query(tableGateRecord,
                columns: [
                  columnGateRecordTime,
                  columnGateRecordGateMac,
                  columnGateRecordOperator,
                  columnGateRecordPasserby,
                  columnGateName,
                  columnGateRecordControlType,
                ],
                where: "$columnGateRecordTime < ?",
                whereArgs: [endTime]);
          }
        }
      } else {
        if (operator!.isNotEmpty) {
          if (passerby!.isNotEmpty) {
            maps = await db!.query(tableGateRecord,
                columns: [
                  columnGateRecordTime,
                  columnGateRecordGateMac,
                  columnGateRecordOperator,
                  columnGateRecordPasserby,
                  columnGateName,
                  columnGateRecordControlType,
                ],
                where:
                    "$columnGateRecordOperator = ? and $columnGateRecordPasserby = ? ",
                whereArgs: [operator, passerby]);
          } else {
            maps = await db!.query(tableGateRecord,
                columns: [
                  columnGateRecordTime,
                  columnGateRecordGateMac,
                  columnGateRecordOperator,
                  columnGateRecordPasserby,
                  columnGateName,
                  columnGateRecordControlType,
                ],
                where: "$columnGateRecordOperator = ?",
                whereArgs: [operator]);
          }
        } else {
          if (passerby!.isNotEmpty) {
            maps = await db!.query(tableGateRecord,
                columns: [
                  columnGateRecordTime,
                  columnGateRecordGateMac,
                  columnGateRecordOperator,
                  columnGateRecordPasserby,
                  columnGateName,
                  columnGateRecordControlType,
                ],
                where: "$columnGateRecordPasserby = ?",
                whereArgs: [passerby]);
          } else {
            maps = await db!.rawQuery("select * from $tableGateRecord");
          }
        }
      }
    }

    if (maps.length > 0) {
      List<GateRecordModel> gateRecords = [];
      maps.forEach((element) {
        gateRecords.add(GateRecordModel.fromMap(element));
      });
      close();
      return gateRecords;
    }
    close();
    return null;
  }
}
